﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
/// <summary>
/// Summary description for DALTask
/// </summary>
public class DALTask
{
    static string connStr = ConfigurationManager.ConnectionStrings["SkillifyConnectionString"].ConnectionString;
   // private static string myCS = "Data Source=.; Initial Catalog =DuplicateKAT;Integrated Security=True";
    private static SqlConnection myCon = new SqlConnection(connStr);
    static SqlCommand cmd;
    static SqlDataAdapter da;
    static DataTable dt;
    public DALTask()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public static void SetTaskType(string TaskName,string Value,string Type,int TransType)
    {
        cmd = new SqlCommand();
        cmd.Connection =  myCon;
        cmd.CommandText = "sp_GetSetTaskTypeMaster";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@vc_TaskName",TaskName );
        cmd.Parameters.AddWithValue("@vc_rbtnFollowUp",Value);
        cmd.Parameters.AddWithValue("@vc_FollowUpType",Type );
        cmd.Parameters.AddWithValue("@int_TransType",TransType);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        if (myCon.State == ConnectionState.Open)
            myCon.Close();
        try
        {
            myCon.Open();
            cmd.ExecuteNonQuery();
            
        }
        catch
        {
        }
        finally
        {
            myCon.Close();
        }
        
    }
    public static void SetTask(string TaskName, string TaskDes, int TaskType, int TaskOwner, int SubjectId, string topic, int LibraryId, int IsVerify, int TestName, DateTime StartDate, DateTime EndDate,string UserId, int Status, string Remarks,int TransType)
  
    {
       cmd = new SqlCommand();
       
        cmd.Connection =  myCon;
        cmd.CommandText = "sp_GetSetTask";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@vc_TaskName",TaskName );
        cmd.Parameters.AddWithValue("@vc_TaskDesc", TaskDes);
        cmd.Parameters.AddWithValue("@int_TaskTypeID", TaskType);
        cmd.Parameters.AddWithValue("@int_TaskOwnerID", TaskOwner);
          
        cmd.Parameters.AddWithValue("@int_SubjectID",SubjectId);
        cmd.Parameters.AddWithValue("@vc_TopicId",topic);
        cmd.Parameters.AddWithValue("@int_LibraryID",LibraryId);
        cmd.Parameters.AddWithValue("@int_IsVerificationReq", IsVerify);

        cmd.Parameters.AddWithValue("@int_TestID", TestName);
        cmd.Parameters.AddWithValue("@dt_StartDate",StartDate);
        cmd.Parameters.AddWithValue("@dt_EndDate",EndDate );

        //cmd.Parameters.AddWithValue("@int_GroupId", GroupId);
        cmd.Parameters.AddWithValue("@vc_UserId", UserId);
        cmd.Parameters.AddWithValue("@int_Status", Status);
        cmd.Parameters.AddWithValue("@vc_Remarks ", Remarks);
        cmd.Parameters.AddWithValue("@int_TransType",TransType);
       if (myCon.State == ConnectionState.Open)
            myCon.Close();
       try
       {
           myCon.Open();
           cmd.ExecuteNonQuery();
           
       }
       catch (Exception ex)
       {

       }

       finally
       {
           myCon.Close();
       }
       
    }
    public static void ModifyTask(int TaskId,string TaskName,string TaskDes,int TaskOwner,int SubjectId,string topic,int LibraryId,int IsVerify,int TestName,string Remarks,int TaskType, DateTime StartDate, DateTime EndDate, string UserId, int Status,  int TransType)
    {
        cmd = new SqlCommand();

        cmd.Connection = myCon;
        cmd.CommandText = "sp_GetSetTask";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@int_TaskId", TaskId);
        cmd.Parameters.AddWithValue("@vc_TaskName", TaskName);
        cmd.Parameters.AddWithValue("@vc_TaskDesc", TaskDes);
        cmd.Parameters.AddWithValue("@int_TaskTypeID", TaskType);
        cmd.Parameters.AddWithValue("@int_TaskOwnerID", TaskOwner);

        cmd.Parameters.AddWithValue("@int_SubjectID", SubjectId);
        cmd.Parameters.AddWithValue("@vc_TopicId", topic);
        cmd.Parameters.AddWithValue("@int_LibraryID", LibraryId);
        cmd.Parameters.AddWithValue("@int_IsVerificationReq", IsVerify);

        cmd.Parameters.AddWithValue("@int_TestID", TestName);
        cmd.Parameters.AddWithValue("@dt_StartDate", StartDate);
        cmd.Parameters.AddWithValue("@dt_EndDate", EndDate);

        //cmd.Parameters.AddWithValue("@int_GroupId", GroupId);
        cmd.Parameters.AddWithValue("@vc_UserId", UserId);
        cmd.Parameters.AddWithValue("@int_Status", Status);
        cmd.Parameters.AddWithValue("@vc_Remarks ", Remarks);
        cmd.Parameters.AddWithValue("@int_TransType", TransType);
        if (myCon.State == ConnectionState.Open)
            myCon.Close();
        try
        {
            myCon.Open();
            cmd.ExecuteNonQuery();

        }
        catch (Exception ex)
        {

        }

        finally
        {
            myCon.Close();
        }

    }
    

    public static void ModifyTaskType(int modifytask,string TaskName, string value, string Type,int TransType)
    {
        cmd = new SqlCommand();
        cmd.Connection = myCon;
        cmd.CommandText = "sp_GetSetTaskTypeMaster";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@vc_TaskName",TaskName );
        cmd.Parameters.AddWithValue("@vc_rbtnFollowUp",value);
        cmd.Parameters.AddWithValue("@vc_FollowUpType",Type);
        cmd.Parameters.AddWithValue("@int_TypeId",modifytask);
        cmd.Parameters.AddWithValue("@int_TransType",TransType);
        if (myCon.State == ConnectionState.Open)
            myCon.Close();
        try
        {
            myCon.Open();
            cmd.ExecuteNonQuery();
            
        }
        catch (Exception ex)
        {

        }

        finally
        {
            myCon.Close();
        }

     
    }
    public static DataTable GetGroup()
    {
        cmd = new SqlCommand();
        cmd.Connection = myCon;
        cmd.CommandText = "sp_GetGroup";
        cmd.CommandType = CommandType.StoredProcedure;
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        if (myCon.State == ConnectionState.Open)
            myCon.Close();
        try
        {
            myCon.Open();
            cmd.ExecuteNonQuery();
            da.Fill(dt);
        }
        catch (Exception ex)
        {

        }

        finally
        {
            myCon.Close();
        }

        return dt;
    }

    public static DataTable GetUser(int groupId)
    {
        cmd = new SqlCommand();
        cmd.Connection = myCon;
        cmd.CommandText = "sp_GetAllUsersorParticularUser";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@GroupId", groupId);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        if (myCon.State == ConnectionState.Open)
            myCon.Close();
        try
        {
            myCon.Open();
            cmd.ExecuteNonQuery();
            da.Fill(dt);
        }
        catch (Exception ex)
        {

        }

        finally
        {
            myCon.Close();
        }

        return dt;
    }
    public static DataTable GetGrid(int  val)
    {

        cmd = new SqlCommand();
        cmd.Connection = myCon;
        cmd.CommandText = "sp_GetSetTaskTypeMaster";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@int_TypeId",val);
        cmd.Parameters.AddWithValue("@int_TransType", 4);
        dt = new DataTable();
        da = new SqlDataAdapter(cmd);
        if (myCon.State == ConnectionState.Open)
            myCon.Close();
        try
        {
            myCon.Open();
            cmd.ExecuteNonQuery();
            da.Fill(dt);
        }
        catch (Exception ex)
        {

        }

        finally
        {
            myCon.Close();
        }

        return dt;
       
    }


    public static DataTable GetSetGrid()
    {
        cmd = new SqlCommand();
        cmd.Connection = myCon;
        cmd.CommandText = "sp_GetSetTaskTypeMaster";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@int_TransType",3);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        if (myCon.State == ConnectionState.Open)
            myCon.Close();
        try
        {
            myCon.Open();
            cmd.ExecuteNonQuery();
            da.Fill(dt);
        }
        catch (Exception ex)
        {
           
        }

        finally
        {
            myCon.Close();
        }

        return dt;


       
      
    }


    public static DataTable GetOwner()
    {
        cmd = new SqlCommand();
        cmd.Connection = myCon;
        cmd.CommandText = "sp_GetAllUsers";
        cmd.CommandType = CommandType.StoredProcedure;
        
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        if (myCon.State == ConnectionState.Open)
            myCon.Close();
        try
        {
            myCon.Open();
            cmd.ExecuteNonQuery();
            da.Fill(dt);
        }
        catch (Exception ex)
        {

        }

        finally
        {
            myCon.Close();
        }

        return dt;
    }

    public static DataTable GetTaskType()
    {
        cmd = new SqlCommand();
        cmd.Connection = myCon;
        cmd.CommandText = "sp_GetSetTaskTypeMaster";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@int_TransType",3);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        if (myCon.State == ConnectionState.Open)
            myCon.Close();
        try
        {
            myCon.Open();
            cmd.ExecuteNonQuery();
            da.Fill(dt);
        }
        catch (Exception ex)
        {

        }

        finally
        {
            myCon.Close();
        }

        return dt;
    }
    public static DataTable GetTestName()
    {
        cmd = new SqlCommand();
        cmd.Connection = myCon;
        cmd.CommandText = "sp_GetTestName";
        cmd.CommandType = CommandType.StoredProcedure;
    
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        if (myCon.State == ConnectionState.Open)
            myCon.Close();
        try
        {
            myCon.Open();
            cmd.ExecuteNonQuery();
            da.Fill(dt);
        }
        catch (Exception ex)
        {

        }

        finally
        {
            myCon.Close();
        }

        return dt;
    }
    public static DataTable GetAllUsers(int groupid,object userid)
    {
        cmd = new SqlCommand();
        cmd.Connection = myCon;
        cmd.CommandText = "sp_GetUsersandGroups";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@groupid",groupid);
        cmd.Parameters.AddWithValue("@userid", userid);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        //if (myCon.State == ConnectionState.Open)
        //    myCon.Close();
        try
        {
            myCon.Open();
            cmd.ExecuteNonQuery();
            da.Fill(dt);
        }
        catch (Exception ex)
        {

        }

        return dt;
    }
    public static DataTable GetManageTaskGrid()
    {
        cmd = new SqlCommand();
        cmd.Connection = myCon;
        cmd.CommandText = "sp_GetSetTask";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@int_TransType", 3);
        da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        
        DataTable dt=new DataTable();
        dt = ds.Tables[0];

        int value = Convert.ToInt32(dt.Rows[0][0]);
      

        if (value > 0)
        {
            return ds.Tables[value + 1];
        }
        else
        {
            return ds.Tables[1];
        }
    }
    public static DataTable DisplayTask(int TaskId)
    {
        cmd = new SqlCommand();
        cmd.Connection = myCon;
        cmd.CommandText = "sp_GetSetTask";
        cmd.CommandType = CommandType.StoredProcedure;
        
        cmd.Parameters.AddWithValue("@int_TaskID", TaskId);
        cmd.Parameters.AddWithValue("@int_TransType", 4);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);

        return dt; 
    }
    


    public static DataTable GetTopicBasedOnTaskId(string topics)
    {
        DataTable dt = new DataTable();
        cmd = new SqlCommand();
        cmd.Connection = myCon;
        cmd.CommandText = "sp_GetTopicRelatedtoTask";
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@vc_TopicId", topics);
        
        da = new SqlDataAdapter(cmd);
        
        da.Fill(dt);

        return dt; 
    }

    public static DataTable GetUsersRelatedtoTaskId(int id,int type)
    {
        cmd = new SqlCommand();
        cmd.Connection = myCon;
        cmd.CommandText = "sp_GetSetTask";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("int_TaskID", id);
        cmd.Parameters.AddWithValue("int_TransType",type);

        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);

        return dt; 

    }
}